
import geopandas as gpd
import pandas as pd
import numpy as np
from shapely.geometry import Point

from matplotlib import pyplot as plt
import seaborn as sns

import hvplot.pandas
import holoviews as hv


# Show all columns
pd.options.display.max_columns = 999


# Download data on magnitude 2.5+ quakes from the past week
endpoint_url = "http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_week.geojson"
df = gpd.read_file(endpoint_url)


df.head()


fig, ax = plt.subplots(figsize=(10, 10))

# plot the country outline
world = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
world.plot(ax=ax, facecolor="none", edgecolor="black")

# plot the earthquakes
df.plot(ax=ax, color="crimson", markersize=30, edgecolor='k', linewidth=0.5)

ax.set_axis_off()


# base URL
url = "https://services.arcgis.com/fLeGjb7u4uXqeF9q/arcgis/rest/services/Zipcodes_Poly/FeatureServer/0/"


import esri2gpd


zip_codes = esri2gpd.get(url)


zip_codes.head()


zip_codes.crs

<Geographic 2D CRS: EPSG:4326>
Name: WGS 84
Axis Info [ellipsoidal]:
- Lat[north]: Geodetic latitude (degree)
- Lon[east]: Geodetic longitude (degree)
Area of Use:
- name: World.
- bounds: (-180.0, -90.0, 180.0, 90.0)
Datum: World Geodetic System 1984 ensemble
- Ellipsoid: WGS 84
- Prime Meridian: Greenwich


fig, ax = plt.subplots(figsize=(6, 6))
zip_codes.to_crs(epsg=3857).plot(ax=ax, facecolor="none", edgecolor="black")
ax.set_axis_off()


import requests


# the API end point
API_endpoint = "https://phl.carto.com/api/v2/sql"

# the query
query = "SELECT * FROM shootings" # table name is "shootings"

# desired format of the returned features
output_format = 'geojson'

# fields to skip
skipfields = ["cartodb_id"]


# all of our request parameters
params = dict(q=query, format=output_format, skipfields=skipfields)

params

{'q': 'SELECT * FROM shootings',
 'format': 'geojson',
 'skipfields': ['cartodb_id']}


# make the request
r = requests.get(API_endpoint, params=params)

r

<Response [200]>


# Get the returned data in JSON format
# This is a dictionary
features = r.json()


type(features)

dict


# What are the keys?
list(features.keys())

['type', 'features']


features['type']

'FeatureCollection'


# Let's look at the first feature
features['features'][0]

{'type': 'Feature',
 'geometry': {'type': 'Point', 'coordinates': [-75.119383, 39.98878]},
 'properties': {'objectid': 5419571,
  'year': 2020,
  'dc_key': '202024002926.0',
  'code': '111',
  'date_': '2020-01-11T00:00:00Z',
  'time': '23:37:00',
  'race': 'B',
  'sex': 'M',
  'age': '23',
  'wound': 'Multiple',
  'officer_involved': 'N',
  'offender_injured': 'N',
  'offender_deceased': 'N',
  'location': '2000 BLOCK E Rush St',
  'latino': 0,
  'point_x': None,
  'point_y': None,
  'dist': '24',
  'inside': 0,
  'outside': 1,
  'fatal': 1}}


shootings = gpd.GeoDataFrame.from_features(features, crs="EPSG:4326")


shootings.head()


query = "SELECT COUNT(*) FROM shootings"


params = dict(q=query)
r = requests.get(API_endpoint, params=params)


r.json()

{'rows': [{'cartodb_id': 1,
   'the_geom': '0101000020E6100000DC40AAF6A3C752C06318665790FE4340',
   'the_geom_webmercator': '0101000020110F0000828A6FDA42E65FC123F590FC4D8E5241',
   'objectid': 5419571,
   'year': 2020,
   'dc_key': '202024002926.0',
   'code': '111',
   'date_': '2020-01-11T00:00:00Z',
   'time': '23:37:00',
   'race': 'B',
   'sex': 'M',
   'age': '23',
   'wound': 'Multiple',
   'officer_involved': 'N',
   'offender_injured': 'N',
   'offender_deceased': 'N',
   'location': '2000 BLOCK E Rush St',
   'latino': 0,
   'point_x': None,
   'point_y': None,
   'dist': '24',
   'inside': 0,
   'outside': 1,
   'fatal': 1}],
 'time': 0.002,
 'fields': {'cartodb_id': {'type': 'number', 'pgtype': 'int8'},
  'the_geom': {'type': 'geometry',
   'wkbtype': 'Unknown',
   'dims': 2,
   'srid': 4326},
  'the_geom_webmercator': {'type': 'geometry',
   'wkbtype': 'Unknown',
   'dims': 2,
   'srid': 3857},
  'objectid': {'type': 'number', 'pgtype': 'numeric'},
  'year': {'type': 'number', 'pgtype': 'numeric'},
  'dc_key': {'type': 'string', 'pgtype': 'text'},
  'code': {'type': 'string', 'pgtype': 'text'},
  'date_': {'type': 'date', 'pgtype': 'date'},
  'time': {'type': 'string', 'pgtype': 'text'},
  'race': {'type': 'string', 'pgtype': 'text'},
  'sex': {'type': 'string', 'pgtype': 'text'},
  'age': {'type': 'string', 'pgtype': 'text'},
  'wound': {'type': 'string', 'pgtype': 'text'},
  'officer_involved': {'type': 'string', 'pgtype': 'text'},
  'offender_injured': {'type': 'string', 'pgtype': 'text'},
  'offender_deceased': {'type': 'string', 'pgtype': 'text'},
  'location': {'type': 'string', 'pgtype': 'text'},
  'latino': {'type': 'number', 'pgtype': 'numeric'},
  'point_x': {'type': 'number', 'pgtype': 'numeric'},
  'point_y': {'type': 'number', 'pgtype': 'numeric'},
  'dist': {'type': 'string', 'pgtype': 'text'},
  'inside': {'type': 'number', 'pgtype': 'numeric'},
  'outside': {'type': 'number', 'pgtype': 'numeric'},
  'fatal': {'type': 'number', 'pgtype': 'numeric'}},
 'total_rows': 1}


# select the first 5
query = "SELECT * FROM shootings LIMIT 1"


params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)


df = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
df


shootings.head()


query = "SELECT * FROM shootings WHERE fatal = 0"


# Make the request
params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)

# Make the GeoDataFrame
fatal = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
print("number of nonfatal shootings = ", len(fatal))

number of nonfatal shootings =  10434


query = "SELECT * FROM shootings WHERE date_ > '12/31/21'"


# Make the request
params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)

# Make the GeoDataFrame
this_year = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
print("number of shootings this year = ", len(this_year))

number of shootings this year =  1822


import carto2gpd


where = "date_ > '12/31/21' and fatal = 0"
df = carto2gpd.get(API_endpoint, 'shootings', where=where)


df.head()


# Limit results to the first 5
df = carto2gpd.get(API_endpoint, 'shootings', limit=5)


len(df)

5

df


size = carto2gpd.get_size(API_endpoint, 'shootings')
print(size)

13241


# First step: import cenpy
import cenpy


# UNCOMMENT TO SEE ALL ROWS/COLUMNS IN DATAFRAMES
pd.options.display.max_rows = 999 
pd.options.display.max_colwidth = 200


available = cenpy.explorer.available()

available.head()


# Return a dataframe of all datasets that start with "ACS"
# Axis=0 means to filter the index labels!
acs = available.filter(regex="^ACS", axis=0)

acs


# Return a dataframe of all datasets that start with "ACSDT5Y"
available.filter(regex="^ACSDT5Y", axis=0)


cenpy.explorer.explain("ACSDT5Y2020")

{'American Community Survey: 5-Year Estimates: Detailed Tables 5-Year': 'The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population. Summary files include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts (117th Congress), all counties, all places, and all tracts and block groups. Summary files contain the most detailed cross-tabulations, many of which are published down to block groups. The data are population and housing counts. There are over 64,000 variables in this dataset.'}


acs = cenpy.remote.APIConnection("ACSDT5Y2020")


len(acs.variables)

27892


acs.variables.head(n=10)


acs.varslike?

Signature: acs.varslike(pattern=None, by=None, engine='re', within=None)
Docstring:
Grabs columns that match a particular search pattern.

Parameters
----------
pattern : str
          a search pattern to match
by      : str
          a column in the APIConnection.variables to conduct the search
          within
engine  : {'re', 'fnmatch', callable}
          backend string matching module to use, or a function of the form
          match(candidate, pattern). (default: 're')
within  : pandas.DataFrame 
          the variables over which to search.

Notes
------
Only regex and fnmatch will be supported modules. Note that, while
regex is the default, the python regular expressions module has some
strange behavior if you're used to VIM or Perl-like regex. It may be
easier to use fnmatch if regex is not providing the results you expect.

If you want, you can also pass an engine that is a function. If so, this
needs to be a function that has a signature like:

fn(candidate, pattern)

and return True or False if the candidate matches the pattern. So, for
instance, you can use any string processing function:

    >>> cxn.varslike('_100M', engine = lambda c,p: c.endswith(p)

which may also be expressed as a regexp:

    >>> cxn.varslike('_100M$', engine='re')

or an fnmatch pattern:

    >>> cxn.varslike('*_100M', engine='fnmatch')
File:      ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py
Type:      method


acs.varslike("HISPANIC OR LATINO ORIGIN BY RACE", by='concept').sort_index() # searches along concept column


variables = [
    "NAME",
    "B03002_001E", # Total
    "B03002_003E", # Not Hispanic, White
    "B03002_004E", # Not Hispanic, Black
    "B03002_005E", # Not Hispanic, American Indian
    "B03002_006E", # Not Hispanic, Asian
    "B03002_007E", # Not Hispanic, Native Hawaiian
    "B03002_008E", # Not Hispanic, Other
    "B03002_009E", # Not Hispanic, Two or More Races
    "B03002_012E", # Hispanic
]


acs.geographies['fips']


counties = cenpy.explorer.fips_table("COUNTY")
counties.head()


# Trim to just Philadelphia
# Search for rows where name contains "Philadelphia"
counties.loc[ counties[3].str.contains("Philadelphia") ]


philly_county_code = "101"
pa_state_code = "42"


philly_demo_data = acs.query(
    cols=variables,
    geo_unit="block group:*",
    geo_filter={"state": pa_state_code, 
                "county": philly_county_code, 
                "tract": "*"},
)


philly_demo_data.head()


for variable in variables:
    
    # Convert all variables EXCEPT for NAME
    if variable != "NAME":
        philly_demo_data[variable] = philly_demo_data[variable].astype(float)


acs.query(
    cols=variables,
    geo_unit="block group:*",
    geo_filter={"state": pa_state_code},
)

/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:224: SyntaxWarning: "is not" with a literal. Did you mean "!="?
  if index is not "":
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:224: SyntaxWarning: "is not" with a literal. Did you mean "!="?
  if index is not "":

---------------------------------------------------------------------------
JSONDecodeError                           Traceback (most recent call last)
File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/requests/models.py:971, in Response.json(self, **kwargs)
    970 try:
--> 971     return complexjson.loads(self.text, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/__init__.py:525, in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, use_decimal, **kw)
    521 if (cls is None and encoding is None and object_hook is None and
    522         parse_int is None and parse_float is None and
    523         parse_constant is None and object_pairs_hook is None
    524         and not use_decimal and not kw):
--> 525     return _default_decoder.decode(s)
    526 if cls is None:

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/decoder.py:370, in JSONDecoder.decode(self, s, _w, _PY3)
    369     s = str(s, self.encoding)
--> 370 obj, end = self.raw_decode(s)
    371 end = _w(s, end).end()

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/decoder.py:400, in JSONDecoder.raw_decode(self, s, idx, _w, _PY3)
    399         idx += 3
--> 400 return self.scan_once(s, idx=_w(s, idx).end())

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

JSONDecodeError                           Traceback (most recent call last)
File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:219, in APIConnection.query(self, cols, geo_unit, geo_filter, apikey, **kwargs)
    218 try:
--> 219     json_content = res.json()
    220     df = pd.DataFrame().from_records(json_content[1:], columns=json_content[0])

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/requests/models.py:975, in Response.json(self, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError
--> 975     raise RequestsJSONDecodeError(e.msg, e.doc, e.pos)

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

HTTPError                                 Traceback (most recent call last)
Input In [75], in <cell line: 1>()
----> 1 acs.query(
      2     cols=variables,
      3     geo_unit="block group:*",
      4     geo_filter={"state": pa_state_code},
      5 )

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:229, in APIConnection.query(self, cols, geo_unit, geo_filter, apikey, **kwargs)
    227 except (ValueError, JSONDecodeError):
    228     if res.status_code == 400:
--> 229         raise r.HTTPError(
    230             "400 " + "\n".join(map(lambda x: x.decode(), res.iter_lines()))
    231         )
    232     else:
    233         res.raise_for_status()

HTTPError: 400 error: unknown/unsupported geography heirarchy


cenpy.tiger.available()

[{'name': 'AIANNHA', 'type': 'MapServer'},
 {'name': 'CBSA', 'type': 'MapServer'},
 {'name': 'Hydro', 'type': 'MapServer'},
 {'name': 'Labels', 'type': 'MapServer'},
 {'name': 'Legislative', 'type': 'MapServer'},
 {'name': 'Places_CouSub_ConCity_SubMCD', 'type': 'MapServer'},
 {'name': 'PUMA_TAD_TAZ_UGA_ZCTA', 'type': 'MapServer'},
 {'name': 'Region_Division', 'type': 'MapServer'},
 {'name': 'School', 'type': 'MapServer'},
 {'name': 'Special_Land_Use_Areas', 'type': 'MapServer'},
 {'name': 'State_County', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2012', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2013', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2014', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2015', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2016', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2017', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2018', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2019', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2021', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2022', 'type': 'MapServer'},
 {'name': 'tigerWMS_Census2010', 'type': 'MapServer'},
 {'name': 'tigerWMS_Census2020', 'type': 'MapServer'},
 {'name': 'tigerWMS_Current', 'type': 'MapServer'},
 {'name': 'tigerWMS_ECON2012', 'type': 'MapServer'},
 {'name': 'tigerWMS_PhysicalFeatures', 'type': 'MapServer'},
 {'name': 'Tracts_Blocks', 'type': 'MapServer'},
 {'name': 'Transportation', 'type': 'MapServer'},
 {'name': 'TribalTracts', 'type': 'MapServer'},
 {'name': 'Urban', 'type': 'MapServer'},
 {'name': 'USLandmass', 'type': 'MapServer'}]


acs.set_mapservice("tigerWMS_Census2020")

Connection to American Community Survey: 5-Year Estimates: Detailed Tables 5-Year(ID: https://api.census.gov/data/id/ACSDT5Y2020)
With MapServer: Census 2020 WMS


acs.mapservice.layers

[(ESRILayer) Urban Growth Areas,
 (ESRILayer) Urban Growth Areas Labels,
 (ESRILayer) Tribal Census Tracts,
 (ESRILayer) Tribal Census Tracts Labels,
 (ESRILayer) Tribal Block Groups,
 (ESRILayer) Tribal Block Groups Labels,
 (ESRILayer) Census Tracts,
 (ESRILayer) Census Tracts Labels,
 (ESRILayer) Census Block Groups,
 (ESRILayer) Census Block Groups Labels,
 (ESRILayer) Census Blocks,
 (ESRILayer) Census Blocks Labels,
 (ESRILayer) Unified School Districts,
 (ESRILayer) Unified School Districts Labels,
 (ESRILayer) Secondary School Districts,
 (ESRILayer) Secondary School Districts Labels,
 (ESRILayer) Elementary School Districts,
 (ESRILayer) Elementary School Districts Labels,
 (ESRILayer) Estates,
 (ESRILayer) Estates Labels,
 (ESRILayer) County Subdivisions,
 (ESRILayer) County Subdivisions Labels,
 (ESRILayer) Subbarrios,
 (ESRILayer) Subbarrios Labels,
 (ESRILayer) Consolidated Cities,
 (ESRILayer) Consolidated Cities Labels,
 (ESRILayer) Incorporated Places,
 (ESRILayer) Incorporated Places Labels,
 (ESRILayer) Census Designated Places,
 (ESRILayer) Census Designated Places Labels,
 (ESRILayer) Alaska Native Regional Corporations,
 (ESRILayer) Alaska Native Regional Corporations Labels,
 (ESRILayer) Tribal Subdivisions,
 (ESRILayer) Tribal Subdivisions Labels,
 (ESRILayer) Federal American Indian Reservations,
 (ESRILayer) Federal American Indian Reservations Labels,
 (ESRILayer) Off-Reservation Trust Lands,
 (ESRILayer) Off-Reservation Trust Lands Labels,
 (ESRILayer) State American Indian Reservations,
 (ESRILayer) State American Indian Reservations Labels,
 (ESRILayer) Hawaiian Home Lands,
 (ESRILayer) Hawaiian Home Lands Labels,
 (ESRILayer) Alaska Native Village Statistical Areas,
 (ESRILayer) Alaska Native Village Statistical Areas Labels,
 (ESRILayer) Oklahoma Tribal Statistical Areas,
 (ESRILayer) Oklahoma Tribal Statistical Areas Labels,
 (ESRILayer) State Designated Tribal Statistical Areas,
 (ESRILayer) State Designated Tribal Statistical Areas Labels,
 (ESRILayer) Tribal Designated Statistical Areas,
 (ESRILayer) Tribal Designated Statistical Areas Labels,
 (ESRILayer) American Indian Joint-Use Areas,
 (ESRILayer) American Indian Joint-Use Areas Labels,
 (ESRILayer) 116th Congressional Districts,
 (ESRILayer) 116th Congressional Districts Labels,
 (ESRILayer) 2018 State Legislative Districts - Upper,
 (ESRILayer) 2018 State Legislative Districts - Upper Labels,
 (ESRILayer) 2018 State Legislative Districts - Lower,
 (ESRILayer) 2018 State Legislative Districts - Lower Labels,
 (ESRILayer) Voting Districts,
 (ESRILayer) Voting Districts Labels,
 (ESRILayer) Census Divisions,
 (ESRILayer) Census Divisions Labels,
 (ESRILayer) Census Regions,
 (ESRILayer) Census Regions Labels,
 (ESRILayer) Combined New England City and Town Areas,
 (ESRILayer) Combined New England City and Town Areas Labels,
 (ESRILayer) New England City and Town Area Divisions,
 (ESRILayer) New England City and Town Area  Divisions Labels,
 (ESRILayer) Metropolitan New England City and Town Areas,
 (ESRILayer) Metropolitan New England City and Town Areas Labels,
 (ESRILayer) Micropolitan New England City and Town Areas,
 (ESRILayer) Micropolitan New England City and Town Areas Labels,
 (ESRILayer) Combined Statistical Areas,
 (ESRILayer) Combined Statistical Areas Labels,
 (ESRILayer) Metropolitan Divisions,
 (ESRILayer) Metropolitan Divisions Labels,
 (ESRILayer) Metropolitan Statistical Areas,
 (ESRILayer) Metropolitan Statistical Areas Labels,
 (ESRILayer) Micropolitan Statistical Areas,
 (ESRILayer) Micropolitan Statistical Areas Labels,
 (ESRILayer) States,
 (ESRILayer) States Labels,
 (ESRILayer) Counties,
 (ESRILayer) Counties Labels,
 (ESRILayer) Zip Code Tabulation Areas,
 (ESRILayer) Zip Code Tabulation Areas Labels,
 (ESRILayer) Public Use Microdata Areas,
 (ESRILayer) Public Use Microdata Areas Labels]


block_group_geoservice = acs.mapservice.layers[8]

block_group_geoservice

(ESRILayer) Census Block Groups


block_group_geoservice.variables


# The base url for the map service API endpoint
block_group_geoservice._baseurl

'http://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/tigerWMS_Census2020/MapServer/8'


## We're just querying a GeoService — let's use esri2gpd

# Only Philadelphia
where_clause = "STATE = '42' AND COUNTY = '101'"

# Query
philly_block_groups = esri2gpd.get(block_group_geoservice._baseurl, where=where_clause)


len(philly_block_groups)

1338


philly_block_groups.head()


philly_demo_final = philly_block_groups.merge(
    philly_demo_data,
    left_on=["STATE", "COUNTY", "TRACT", "BLKGRP"],
    right_on=["state", "county", "tract", "block group"],
)


philly_demo_final.head()


# Check the CRS
philly_demo_final.crs

<Geographic 2D CRS: EPSG:4326>
Name: WGS 84
Axis Info [ellipsoidal]:
- Lat[north]: Geodetic latitude (degree)
- Lon[east]: Geodetic longitude (degree)
Area of Use:
- name: World.
- bounds: (-180.0, -90.0, 180.0, 90.0)
Datum: World Geodetic System 1984 ensemble
- Ellipsoid: WGS 84
- Prime Meridian: Greenwich


fig, ax = plt.subplots(figsize=(10,10))

# Plot the choropleth
philly_demo_final.plot(ax=ax, column='B03002_001E', legend=True)

# Format
ax.set_title("Population of Philadelphia by Block Group", fontsize=16)
ax.set_axis_off()


cols = ['NAME_x', 'B03002_001E', 'geometry']
philly_demo_final[cols].hvplot(c='B03002_001E',
                              geo=True, 
                              legend=True,
                              width=600, 
                              height=400, 
                              cmap='viridis',
                              frame_height=800, 
                              frame_width=800, 
                              hover_cols=["NAME_x"])


# Rename columns
philly_demo_final = philly_demo_final.rename(
    columns={
        "B03002_001E": "Total",  # Total
        "B03002_003E": "White",  # Not Hispanic, White
        "B03002_004E": "Black",  # Not Hispanic, Black
        "B03002_005E": "AI/AN",  # Not Hispanic, American Indian
        "B03002_006E": "Asian",  # Not Hispanic, Asian
        "B03002_007E": "NH/PI",  # Not Hispanic, Native Hawaiian
        "B03002_008E": "Other_",  # Not Hispanic, Other
        "B03002_009E": "Two Plus",  # Not Hispanic, Two or More Races
        "B03002_012E": "Hispanic",  # Hispanic
    }
)


# Add an "Other" column 
cols = ['AI/AN', 'NH/PI','Other_', 'Two Plus']
philly_demo_final['Other'] = philly_demo_final[cols].sum(axis=1)


def random_points_in_polygon(number, polygon):
    """
    Generate a random number of points within the 
    specified polygon.
    """
    points = []
    min_x, min_y, max_x, max_y = polygon.bounds
    i= 0
    while i < number:
        point = Point(np.random.uniform(min_x, max_x), np.random.uniform(min_y, max_y))
        if polygon.contains(point):
            points.append(point)
            i += 1
    return points


# get the first block group polygon in the data set
geo = philly_demo_final.iloc[0].geometry

geo


fig, ax = plt.subplots(figsize=(6, 6))

# Generate some random points
random_points = random_points_in_polygon(100, geo)

# Plot random points
gpd.GeoSeries(random_points).plot(ax=ax, markersize=20, color="red")

# Plot boundary of block group
gpd.GeoSeries([geo]).plot(ax=ax, facecolor="none", edgecolor="black")

ax.set_axis_off()


def generate_dot_map(data, people_per_dot):
    """
    Given a GeoDataFrame with demographic columns, generate a dot 
    map according to the population in each geometry.
    """
    results = []
    for field in ["White", "Hispanic", "Black", "Asian", "Other"]:

        # generate random points
        pts = data.apply(
            lambda row: random_points_in_polygon(
                row[field] / people_per_dot, row["geometry"]
            ),
            axis=1,
        )

        # combine into single GeoSeries
        pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
        pts.name = "geometry"

        # make into a GeoDataFrame
        pts = gpd.GeoDataFrame(pts)
        pts["field"] = field

        # save
        results.append(pts)

    return gpd.GeoDataFrame(pd.concat(results), crs=data["geometry"].crs).reset_index(
        drop=True
    )


dot_map = generate_dot_map(philly_demo_final, people_per_dot=50)

/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_53673/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_53673/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_53673/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_53673/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_53673/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)


print("number of points = ", len(dot_map))

number of points =  34143


dot_map.tail()


# setup a custom color map from ColorBrewer
from matplotlib.colors import ListedColormap

cmap = ListedColormap(
    ["#3a833c", "#377eb8", "#4daf4a", "#984ea3", "#ff7f00", "#ffff33"]
)


# Convert to 3857
dot_map_3857 = dot_map.to_crs(epsg=3857)


# Initialize the figure and axes
fig, ax = plt.subplots(figsize=(10, 10), facecolor="#cfcfcf")

# Plot
dot_map_3857.plot(
    ax=ax,
    column="field",
    categorical=True,
    legend=True,
    alpha=1,
    markersize=0.5,
    cmap=cmap,
)

# format
ax.set_title("Philadelphia, PA", fontsize=16)
ax.text(
    0.5, 0.95, "1 dot = 50 people", fontsize=12, transform=ax.transAxes, ha="center"
)
ax.set_axis_off()

	id	mag	place	time	updated	tz	url	detail	felt	cdi	mmi	alert	status	sig	net	code	ids	sources	types	nst	dmin	rms	gap	magType	type	title	geometry
0	us6000iqid	5.20	Reykjanes Ridge	1664820502598	1664821283040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	416	us	6000iqid	,us6000iqid,	,us,	,origin,phase-data,	68.0	9.10600	1.5100	52.00	mb	earthquake	M 5.2 - Reykjanes Ridge	POINT Z (-35.50950 53.78860 10.00000)
1	ak022coqg5qq	3.00	35 km NNW of Chenega, Alaska	1664816402103	1664817258582	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	1.0	2.0	NaN	None	automatic	139	ak	022coqg5qq	,ak022coqg5qq,	,ak,	,dyfi,origin,phase-data,	NaN	NaN	0.3600	NaN	ml	earthquake	M 3.0 - 35 km NNW of Chenega, Alaska	POINT Z (-148.24140 60.36270 4.90000)
2	us6000iqfl	4.40	50 km ENE of Mutsu, Japan	1664815161466	1664817497040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	298	us	6000iqfl	,us6000iqfl,	,us,	,origin,phase-data,	30.0	0.86600	0.3900	131.00	mb	earthquake	M 4.4 - 50 km ENE of Mutsu, Japan	POINT Z (141.76760 41.47400 84.99000)
3	ci40355088	3.01	4km W of Banning, CA	1664813289970	1664821582254	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	98.0	3.1	3.537	None	reviewed	170	ci	40355088	,ci40355088,us6000iqfh,	,ci,us,	,dyfi,focal-mechanism,nearby-cities,origin,pha...	114.0	0.04923	0.1700	20.00	ml	earthquake	M 3.0 - 4km W of Banning, CA	POINT Z (-116.92150 33.92467 7.62000)
4	nn00848420	3.10	Nevada	1664810671035	1664812724040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	148	nn	00848420	,us6000iqfb,nn00848420,	,us,nn,	,origin,phase-data,	9.0	0.55200	0.1788	110.67	ml	earthquake	M 3.1 - Nevada	POINT Z (-117.08790 40.82730 7.90000)

	geometry	OBJECTID	CODE	COD	Shape__Area	Shape__Length
0	POLYGON ((-75.11107 40.04682, -75.11206 40.047...	1	19120	20	9.177970e+07	49921.544063
1	POLYGON ((-75.19227 39.99463, -75.19240 39.994...	2	19121	21	6.959879e+07	39534.887217
2	POLYGON ((-75.15406 39.98601, -75.15494 39.986...	3	19122	22	3.591632e+07	24124.645221
3	POLYGON ((-75.15190 39.97056, -75.15258 39.970...	4	19123	23	3.585175e+07	26421.728982
4	POLYGON ((-75.09660 40.04249, -75.09661 40.042...	5	19124	24	1.448080e+08	63658.770420

	c_isTimeseries	c_isMicrodata	publisher	temporal	spatial	programCode	modified	keyword	contactPoint	distribution	description	bureauCode	accessLevel	title	c_isAvailable	c_isCube	c_isAggregate	c_dataset	vintage
ABSCB2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-04-30 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2017.0
ABSCB2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-10-26 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:Erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2018.0
ABSCB2019	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2021-08-17 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:ERD.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2019/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2019.0
ABSCBO2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-04-30 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/abscbo', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscbo,)	2017.0
ABSCBO2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-10-26 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/abscbo', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscbo,)	2018.0

	c_isTimeseries	c_isMicrodata	publisher	temporal	spatial	programCode	modified	keyword	contactPoint	distribution	description	bureauCode	accessLevel	title	c_isAvailable	c_isCube	c_isAggregate	c_dataset	vintage
ACSDT5Y2009	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-08-27 13:11:18.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2009/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2009.0
ACSDT5Y2010	NaN	NaN	U.S. Census Bureau	NaN	United States	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2010.0
ACSDT5Y2011	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2011/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2011.0
ACSDT5Y2012	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2012/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2012.0
ACSDT5Y2013	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2013/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2013.0
ACSDT5Y2014	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2014/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2014.0
ACSDT5Y2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-05 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2015.0
ACSDT5Y2016	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-05 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2016/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2016.0
ACSDT5Y2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-08-21 07:11:43.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2017.0
ACSDT5Y2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-22 16:28:02.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:Acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2018.0
ACSDT5Y2019	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-04-03 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:Acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2019/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2019.0
ACSDT5Y2020	NaN	NaN	U.S. Census Bureau	2020/2020	US	006:004	2021-07-13 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2020/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2020.0
ACSDT5YAIAN2010	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-24 07:18:57.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5/aian', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Indian and Alaska Native (AIAN) tables are released every five years. They are available for selected tribal populations, with more detailed tribal categories compared to those in the...	006:07	public	American Community Survey: 5-Year Estimates: American Indian and Alaska Native Detailed Tables 5-Year	True	True	True	(acs, acs5, aian)	2010.0
ACSDT5YAIAN2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-02-13 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5/aian', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Indian and Alaska Native (AIAN) tables are released every five years. They are available for selected tribal populations, with more detailed tribal categories compared to those in the...	006:07	public	ACS 5-Year AIAN Detailed Tables	True	True	True	(acs, acs5, aian)	2015.0
ACSDT5YSPT2010	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-11 14:16:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5/spt', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Selected Population Tables (SPT) are released every five years. They are available for selected race, Hispanic origin, tribal, and ancestry populations.	006:07	public	American Community Survey: 5-Year Estimates: Selected Population Detailed Tables 5-Year	True	True	True	(acs, acs5, spt)	2010.0
ACSDT5YSPT2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-02-18 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5/spt', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Selected Population Tables (SPT) are released every five years. They are available for selected race, Hispanic origin, tribal, and ancestry populations.	006:07	public	American Community Survey: 5-Year Estimates: Selected Population Detailed Tables 5-Year	True	True	True	(acs, acs5, spt)	2015.0

	label	concept	predicateType	group	predicateOnly	hasGeoCollectionSupport	attributes	required
for	Census API FIPS 'for' clause	Census API Geography Specification	fips-for	N/A	True	NaN	NaN	NaN
in	Census API FIPS 'in' clause	Census API Geography Specification	fips-in	N/A	True	NaN	NaN	NaN
ucgid	Uniform Census Geography Identifier clause	Census API Geography Specification	ucgid	N/A	True	True	NaN	NaN
B24022_060E	Estimate!!Total:!!Female:!!Service occupations:!!Food preparation and serving related occupations	SEX BY OCCUPATION AND MEDIAN EARNINGS IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) FOR THE FULL-TIME, YEAR-ROUND CIVILIAN EMPLOYED POPULATION 16 YEARS AND OVER	int	B24022	NaN	NaN	B24022_060EA,B24022_060M,B24022_060MA	NaN
B19001B_014E	Estimate!!Total:!!$100,000 to $124,999	HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (BLACK OR AFRICAN AMERICAN ALONE HOUSEHOLDER)	int	B19001B	NaN	NaN	B19001B_014EA,B19001B_014M,B19001B_014MA	NaN
B07007PR_019E	Estimate!!Total:!!Moved from different municipio:!!Foreign born:!!Naturalized U.S. citizen	GEOGRAPHICAL MOBILITY IN THE PAST YEAR BY CITIZENSHIP STATUS FOR CURRENT RESIDENCE IN PUERTO RICO	int	B07007PR	NaN	NaN	B07007PR_019EA,B07007PR_019M,B07007PR_019MA	NaN
B19101A_004E	Estimate!!Total:!!$15,000 to $19,999	FAMILY INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (WHITE ALONE HOUSEHOLDER)	int	B19101A	NaN	NaN	B19101A_004EA,B19101A_004M,B19101A_004MA	NaN
B24022_061E	Estimate!!Total:!!Female:!!Service occupations:!!Building and grounds cleaning and maintenance occupations	SEX BY OCCUPATION AND MEDIAN EARNINGS IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) FOR THE FULL-TIME, YEAR-ROUND CIVILIAN EMPLOYED POPULATION 16 YEARS AND OVER	int	B24022	NaN	NaN	B24022_061EA,B24022_061M,B24022_061MA	NaN
B19001B_013E	Estimate!!Total:!!$75,000 to $99,999	HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (BLACK OR AFRICAN AMERICAN ALONE HOUSEHOLDER)	int	B19001B	NaN	NaN	B19001B_013EA,B19001B_013M,B19001B_013MA	NaN
B07007PR_018E	Estimate!!Total:!!Moved from different municipio:!!Foreign born:	GEOGRAPHICAL MOBILITY IN THE PAST YEAR BY CITIZENSHIP STATUS FOR CURRENT RESIDENCE IN PUERTO RICO	int	B07007PR	NaN	NaN	B07007PR_018EA,B07007PR_018M,B07007PR_018MA	NaN

	geometry	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	latino	point_x	point_y	dist	outside	fatal
0	POINT (-75.11938 39.98878)	5419571	2020	202024002926.0	111	2020-01-11T00:00:00Z	23:37:00	B	M	23	Multiple	N	N	N	2000 BLOCK E Rush St	0.0	NaN	NaN	24	1.0	1.0
1	POINT (-75.12721 39.99508)	5419572	2020	202024003147.0	411	2020-01-12T00:00:00Z	20:44:00	B	M	30	Leg	N	N	N	200 BLOCK E Indiana St	1.0	NaN	NaN	24	1.0	0.0
2	POINT (-75.12721 39.99508)	5419573	2020	202024003157.0	411	2020-01-12T00:00:00Z	20:44:00	W	M	24	Hand	N	N	N	200 BLOCK E Indiana St	1.0	NaN	NaN	24	1.0	0.0
3	POINT (-75.12568 39.99644)	5419574	2020	202024004335.0	300	2020-01-16T00:00:00Z	18:45:00	W	M	57	Arm	N	N	N	300 BLOCK E Clearfield St	1.0	NaN	NaN	24	1.0	0.0
4	POINT (-75.12768 39.99779)	5419575	2020	202024004603.0	411	2020-01-17T00:00:00Z	19:30:00	B	M	30	Stomach	N	N	N	100 BLOCK E Wishart St	0.0	NaN	NaN	24	1.0	0.0

	geometry	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	latino	point_x	point_y	dist	outside	fatal	OBJECTID	Shape__Area	Shape__Length
0	POINT (-8362251.464 4864311.954)	5419571	2020	202024002926.0	111	2020-01-11T00:00:00Z	23:37:00	B	M	23	Multiple	N	N	N	2000 BLOCK E Rush St	0.0	NaN	NaN	24	1.0	1.0	1	3.970706e+09	394751.12047
1	POINT (-8363122.205 4865227.054)	5419572	2020	202024003147.0	411	2020-01-12T00:00:00Z	20:44:00	B	M	30	Leg	N	N	N	200 BLOCK E Indiana St	1.0	NaN	NaN	24	1.0	0.0	1	3.970706e+09	394751.12047
2	POINT (-8363122.205 4865227.054)	5419573	2020	202024003157.0	411	2020-01-12T00:00:00Z	20:44:00	W	M	24	Hand	N	N	N	200 BLOCK E Indiana St	1.0	NaN	NaN	24	1.0	0.0	1	3.970706e+09	394751.12047
3	POINT (-8362952.777 4865424.818)	5419574	2020	202024004335.0	300	2020-01-16T00:00:00Z	18:45:00	W	M	57	Arm	N	N	N	300 BLOCK E Clearfield St	1.0	NaN	NaN	24	1.0	0.0	1	3.970706e+09	394751.12047
4	POINT (-8363175.639 4865620.407)	5419575	2020	202024004603.0	411	2020-01-17T00:00:00Z	19:30:00	B	M	30	Stomach	N	N	N	100 BLOCK E Wishart St	0.0	NaN	NaN	24	1.0	0.0	1	3.970706e+09	394751.12047

	geometry	cartodb_id	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	point_x	point_y	dist	outside
0	POINT (-75.15220 39.93557)	8151	5422916	2022	202203023589.0	401	2022-06-13T00:00:00Z	11:55:00	B	F	24	Head	N	N	N	1000 BLOCK S 5TH ST	None	None	3	1
1	POINT (-75.15144 39.93129)	8432	5422917	2022	202203024090.0	401	2022-06-16T00:00:00Z	14:56:00	W	M	31	Hand	N	N	N	1300 BLOCK S 4TH ST	None	None	3	1
2	POINT (-75.21666 39.93809)	8537	5423021	2022	202212010701.0	401	2022-02-26T00:00:00Z	17:49:00	B	F	32	Ankle	N	N	N	5200 BLOCK WOODLAND AVE	None	None	12	1
3	POINT (-75.22222 39.93632)	8538	5423022	2022	202212012893.0	401	2022-03-09T00:00:00Z	23:14:00	B	M	44	Shoulder	N	N	N	5500 BLOCK GREENWAY AVE	None	None	12	1
4	POINT (-75.23698 39.94334)	8539	5423023	2022	202212013020.0	401	2022-03-10T00:00:00Z	18:14:00	B	M	25	Multiple	N	N	N	5700 BLOCK ASHLAND AVE	None	None	12	1

	label	concept	predicateType	group	predicateOnly	hasGeoCollectionSupport	attributes	required
B03002_001E	Estimate!!Total:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_001EA,B03002_001M,B03002_001MA	NaN
B03002_002E	Estimate!!Total:!!Not Hispanic or Latino:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_002EA,B03002_002M,B03002_002MA	NaN
B03002_003E	Estimate!!Total:!!Not Hispanic or Latino:!!White alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_003EA,B03002_003M,B03002_003MA	NaN
B03002_004E	Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_004EA,B03002_004M,B03002_004MA	NaN
B03002_005E	Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_005EA,B03002_005M,B03002_005MA	NaN
B03002_006E	Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_006EA,B03002_006M,B03002_006MA	NaN
B03002_007E	Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_007EA,B03002_007M,B03002_007MA	NaN
B03002_008E	Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_008EA,B03002_008M,B03002_008MA	NaN
B03002_009E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_009EA,B03002_009M,B03002_009MA	NaN
B03002_010E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_010EA,B03002_010M,B03002_010MA	NaN
B03002_011E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_011EA,B03002_011M,B03002_011MA	NaN
B03002_012E	Estimate!!Total:!!Hispanic or Latino:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_012EA,B03002_012M,B03002_012MA	NaN
B03002_013E	Estimate!!Total:!!Hispanic or Latino:!!White alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_013EA,B03002_013M,B03002_013MA	NaN
B03002_014E	Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_014EA,B03002_014M,B03002_014MA	NaN
B03002_015E	Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_015EA,B03002_015M,B03002_015MA	NaN
B03002_016E	Estimate!!Total:!!Hispanic or Latino:!!Asian alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_016EA,B03002_016M,B03002_016MA	NaN
B03002_017E	Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_017EA,B03002_017M,B03002_017MA	NaN
B03002_018E	Estimate!!Total:!!Hispanic or Latino:!!Some other race alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_018EA,B03002_018M,B03002_018MA	NaN
B03002_019E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_019EA,B03002_019M,B03002_019MA	NaN
B03002_020E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_020EA,B03002_020M,B03002_020MA	NaN
B03002_021E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_021EA,B03002_021M,B03002_021MA	NaN
GEO_ID	Geography	SEX BY EDUCATIONAL ATTAINMENT FOR THE POPULATION 25 YEARS AND OVER (SOME OTHER RACE ALONE);SEX BY EDUCATIONAL ATTAINMENT FOR THE POPULATION 25 YEARS AND OVER (TWO OR MORE RACES);SEX BY EDUCATIONAL...	string	B17015,B18104,B17016,B18105,B17017,B18106,B17018,B18107,B19301A,B07001PR,B17011,B17012,B18101,B17013,B18102,B17014,B18103,B19301E,B19301D,B19301C,B19301B,B17010,B19301I,B98013,B99102,B19301H,B9801...	NaN	NaN	NAME	NaN

	name	geoLevelDisplay	referenceDate	requires	wildcard	optionalWithWCFor
0	us	010	2020-01-01	NaN	NaN	NaN
1	region	020	2020-01-01	NaN	NaN	NaN
2	division	030	2020-01-01	NaN	NaN	NaN
3	state	040	2020-01-01	NaN	NaN	NaN
4	county	050	2020-01-01	[state]	[state]	state
5	county subdivision	060	2020-01-01	[state, county]	[county]	county
6	subminor civil division	067	2020-01-01	[state, county, county subdivision]	NaN	NaN
7	place/remainder (or part)	070	2020-01-01	[state, county, county subdivision]	NaN	NaN
8	tract	140	2020-01-01	[state, county]	[county]	county
9	block group	150	2020-01-01	[state, county, tract]	[county, tract]	tract
10	county (or part)	155	2020-01-01	[state, place]	NaN	NaN
11	place	160	2020-01-01	[state]	[state]	state
12	consolidated city	170	2020-01-01	[state]	NaN	NaN
13	place (or part)	172	2020-01-01	[state, consolidated city]	NaN	NaN
14	alaska native regional corporation	230	2020-01-01	[state]	[state]	state
15	american indian area/alaska native area/hawaiian home land	250	2020-01-01	NaN	NaN	NaN
16	tribal subdivision/remainder	251	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
17	american indian area/alaska native area (reservation or statistical entity only)	252	2020-01-01	NaN	NaN	NaN
18	american indian area (off-reservation trust land only)/hawaiian home land	254	2020-01-01	NaN	NaN	NaN
19	tribal census tract	256	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
20	tribal block group	258	2020-01-01	[american indian area/alaska native area/hawaiian home land, tribal census tract]	NaN	NaN
21	state (or part)	260	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
22	place/remainder (or part)	269	2020-01-01	[american indian area/alaska native area/hawaiian home land, state (or part)]	NaN	NaN
23	county (or part)	270	2020-01-01	[american indian area/alaska native area/hawaiian home land, state (or part)]	NaN	NaN
24	american indian area/alaska native area/hawaiian home land (or part)	280	2020-01-01	[state]	NaN	NaN
25	american indian area/alaska native area (reservation or statistical entity only) (or part)	283	2020-01-01	[state]	NaN	NaN
26	american indian area (off-reservation trust land only)/hawaiian home land (or part)	286	2020-01-01	[state]	NaN	NaN
27	state (or part)	290	2020-01-01	[american indian area/alaska native area/hawaiian home land, tribal subdivision/remainder]	NaN	NaN
28	tribal census tract (or part)	291	2020-01-01	[american indian area (reservation only)]	NaN	NaN
29	tribal census tract (or part)	292	2020-01-01	[american indian area (off-reservation trust land only)/hawaiian home land]	NaN	NaN
30	tribal block group (or part)	293	2020-01-01	[american indian area (reservation only), tribal census tract (or part)]	NaN	NaN
31	tribal block group (or part)	294	2020-01-01	[american indian area (off-reservation trust land only)/hawaiian home land, tribal census tract (or part)]	NaN	NaN
32	metropolitan statistical area/micropolitan statistical area	310	2020-01-01	NaN	NaN	NaN
33	state (or part)	311	2020-01-01	[metropolitan statistical area/micropolitan statistical area]	NaN	NaN
34	principal city (or part)	312	2020-01-01	[metropolitan statistical area/micropolitan statistical area, state (or part)]	NaN	NaN
35	county	313	2020-01-01	[metropolitan statistical area/micropolitan statistical area, state (or part)]	NaN	NaN
36	metropolitan division	314	2020-01-01	[metropolitan statistical area/micropolitan statistical area]	NaN	NaN
37	state (or part)	315	2020-01-01	[metropolitan statistical area/micropolitan statistical area, metropolitan division]	NaN	NaN
38	county	316	2020-01-01	[metropolitan statistical area/micropolitan statistical area, metropolitan division, state (or part)]	NaN	NaN
39	metropolitan statistical area/micropolitan statistical area (or part)	320	2020-01-01	[state]	NaN	NaN
40	principal city (or part)	321	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
41	county	322	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
42	metropolitan division (or part)	323	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
43	county	324	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part), metropolitan division (or part)]	NaN	NaN
44	combined statistical area	330	2020-01-01	NaN	NaN	NaN
45	state (or part)	331	2020-01-01	[combined statistical area]	NaN	NaN
46	metropolitan statistical area/micropolitan statistical area	332	2020-01-01	[combined statistical area]	NaN	NaN
47	state (or part)	333	2020-01-01	[combined statistical area, metropolitan statistical area/micropolitan statistical area]	NaN	NaN
48	combined new england city and town area	335	2020-01-01	NaN	NaN	NaN
49	state (or part)	336	2020-01-01	[combined new england city and town area]	[combined new england city and town area]	combined new england city and town area
50	new england city and town area	337	2020-01-01	[combined new england city and town area]	NaN	NaN
51	state (or part)	338	2020-01-01	[combined new england city and town area, new england city and town area]	NaN	NaN
52	combined statistical area (or part)	340	2020-01-01	[state]	NaN	NaN
53	metropolitan statistical area/micropolitan statistical area (or part)	341	2020-01-01	[state, combined statistical area (or part)]	NaN	NaN
54	combined new england city and town area (or part)	345	2020-01-01	[state]	NaN	NaN
55	new england city and town area (or part)	346	2020-01-01	[state, combined new england city and town area (or part)]	NaN	NaN
56	new england city and town area	350	2020-01-01	NaN	NaN	NaN
57	state (or part)	351	2020-01-01	[new england city and town area]	NaN	NaN
58	principal city	352	2020-01-01	[new england city and town area, state (or part)]	NaN	NaN
59	county (or part)	353	2020-01-01	[new england city and town area, state (or part)]	NaN	NaN
60	county subdivision	354	2020-01-01	[new england city and town area, state (or part), county (or part)]	NaN	NaN
61	necta division	355	2020-01-01	[new england city and town area]	NaN	NaN
62	state (or part)	356	2020-01-01	[new england city and town area, necta division]	NaN	NaN
63	county (or part)	357	2020-01-01	[new england city and town area, necta division, state (or part)]	NaN	NaN
64	county subdivision	358	2020-01-01	[new england city and town area, necta division, state (or part), county (or part)]	NaN	NaN
65	new england city and town area (or part)	360	2020-01-01	[state]	NaN	NaN
66	principal city	361	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
67	county (or part)	362	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
68	county subdivision	363	2020-01-01	[state, new england city and town area (or part), county (or part)]	NaN	NaN
69	necta division (or part)	364	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
70	county (or part)	365	2020-01-01	[state, new england city and town area (or part), necta division (or part)]	NaN	NaN
71	county subdivision	366	2020-01-01	[state, new england city and town area (or part), necta division (or part), county (or part)]	NaN	NaN
72	urban area	400	2020-01-01	NaN	NaN	NaN
73	state	410	2020-01-01	[urban area]	NaN	NaN
74	county	430	2020-01-01	[urban area, state]	NaN	NaN
75	congressional district	500	2020-01-01	[state]	[state]	state
76	county (or part)	510	2020-01-01	[state, congressional district]	NaN	NaN
77	american indian area/alaska native area/hawaiian home land (or part)	550	2020-01-01	[state, congressional district]	NaN	NaN
78	state legislative district (upper chamber)	610	2020-01-01	[state]	NaN	NaN
79	county (or part)	612	2020-01-01	[state, state legislative district (upper chamber)]	NaN	NaN
80	state legislative district (lower chamber)	620	2020-01-01	[state]	NaN	NaN
81	county (or part)	622	2020-01-01	[state, state legislative district (lower chamber)]	NaN	NaN
82	public use microdata area	795	2020-01-01	[state]	[state]	state
83	zip code tabulation area	860	2020-01-01	NaN	NaN	NaN
84	school district (elementary)	950	2020-01-01	[state]	[state]	state
85	school district (secondary)	960	2020-01-01	[state]	[state]	state
86	school district (unified)	970	2020-01-01	[state]	[state]	state

	0	1	2	3	4
0	AL	1	1	Autauga County	H1
1	AL	1	3	Baldwin County	H1
2	AL	1	5	Barbour County	H1
3	AL	1	7	Bibb County	H1
4	AL	1	9	Blount County	H1

	NAME	B03002_001E	B03002_003E	B03002_004E	B03002_006E	B03002_009E	B03002_012E	state	county	tract	block group
0	Block Group 3, Census Tract 1.01, Philadelphia County, Pennsylvania	0	0	0	0	0	0	42	101	000101	3
1	Block Group 1, Census Tract 1.02, Philadelphia County, Pennsylvania	1499	1318	30	40	33	78	42	101	000102	1
2	Block Group 4, Census Tract 1.02, Philadelphia County, Pennsylvania	316	316	0	0	0	0	42	101	000102	4
3	Block Group 2, Census Tract 2, Philadelphia County, Pennsylvania	1090	490	40	548	12	0	42	101	000200	2
4	Block Group 2, Census Tract 5, Philadelphia County, Pennsylvania	0	0	0	0	0	0	42	101	000500	2

	name	type	alias	domain	length
0	OBJECTID	esriFieldTypeOID	OBJECTID	None	NaN
1	AREALAND	esriFieldTypeDouble	AREALAND	None	NaN
2	ALANDHIST	esriFieldTypeDouble	ALANDHIST	None	NaN
3	AREAWATER	esriFieldTypeDouble	AREAWATER	None	NaN
4	AWATERHIST	esriFieldTypeDouble	AWATERHIST	None	NaN
5	BLKGRP	esriFieldTypeString	BLKGRP	None	1.0
6	COUNTY	esriFieldTypeString	COUNTY	None	3.0
7	EFFDATE	esriFieldTypeDate	EFFDATE	None	8.0
8	ESTABDATE	esriFieldTypeDate	ESTABDATE	None	8.0
9	FUNCSTAT	esriFieldTypeString	FUNCSTAT	None	1.0
10	GEOID	esriFieldTypeString	GEOID	None	12.0
11	LSADC	esriFieldTypeString	LSADC	None	2.0
12	MTFCC	esriFieldTypeString	MTFCC	None	5.0
13	BASENAME	esriFieldTypeString	BASENAME	None	100.0
14	NAME	esriFieldTypeString	NAME	None	100.0
15	OID	esriFieldTypeString	OID	None	22.0
16	STATE	esriFieldTypeString	STATE	None	2.0
17	TRACT	esriFieldTypeString	TRACT	None	6.0
18	UR	esriFieldTypeString	UR	None	1.0
19	VINTAGE	esriFieldTypeString	VINTAGE	None	2.0
20	CENTLON	esriFieldTypeString	CENTLON	None	12.0
21	CENTLAT	esriFieldTypeString	CENTLAT	None	11.0
22	INTPTLON	esriFieldTypeString	INTPTLON	None	12.0
23	INTPTLAT	esriFieldTypeString	INTPTLAT	None	11.0
24	HU100	esriFieldTypeDouble	HU100	None	NaN
25	POP100	esriFieldTypeDouble	POP100	None	NaN
26	STGEOMETRY	esriFieldTypeGeometry	STGEOMETRY	None	NaN
27	STGEOMETRY.AREA	esriFieldTypeDouble	STGEOMETRY.AREA	None	NaN
28	STGEOMETRY.LEN	esriFieldTypeDouble	STGEOMETRY.LEN	None	NaN

	geometry	OBJECTID	AREALAND	ALANDHIST	BLKGRP	COUNTY	EFFDATE	ESTABDATE	FUNCSTAT	GEOID	LSADC	MTFCC	BASENAME	NAME	OID	STATE	TRACT	UR	VINTAGE	CENTLON	CENTLAT	INTPTLON	INTPTLAT	HU100	POP100	STGEOMETRY.AREA	STGEOMETRY.LEN
0	POLYGON ((-75.05449 40.02465, -75.05405 40.02413, -75.05383 40.02388, -75.05360 40.02362, -75.05337 40.02336, -75.05315 40.02310, -75.05293 40.02286, -75.05270 40.02259, -75.05248 40.02235, -75.05...	82	305092	305092	3	101	NaN	NaN	S	421010323003	BG	G5030	3	Block Group 3	208703717019554	42	032300	None	70	-075.0499467	+40.0222676	-075.0499467	+40.0222676	744	1869	520848.611219	3194.678561
1	POLYGON ((-75.15987 39.94454, -75.15998 39.94401, -75.15841 39.94381, -75.15762 39.94372, -75.15684 39.94361, -75.15676 39.94396, -75.15673 39.94412, -75.15664 39.94451, -75.15654 39.94499, -75.15...	83	70896	70896	1	101	NaN	NaN	S	421010011021	BG	G5030	1	Block Group 1	208703717019574	42	001102	None	70	-075.1581610	+39.9449699	-075.1581610	+39.9449699	1180	1729	120760.631816	1390.110306
2	POLYGON ((-75.00180 40.10493, -75.00165 40.10480, -75.00150 40.10468, -75.00137 40.10456, -75.00134 40.10447, -74.99980 40.10324, -74.99901 40.10258, -74.99873 40.10231, -74.99838 40.10185, -74.99...	904	423561	417638	2	101	NaN	NaN	S	421010361002	BG	G5030	2	Block Group 2	20870508933460	42	036100	None	70	-074.9965242	+40.1037503	-074.9965242	+40.1037503	404	1134	724822.503578	3558.455582
3	POLYGON ((-75.15255 40.00318, -75.15293 40.00159, -75.15163 40.00143, -75.15071 40.00131, -75.14992 40.00121, -75.14915 40.00110, -75.14874 40.00105, -75.14815 40.00099, -75.14847 40.00177, -75.14...	920	192284	192284	2	101	NaN	NaN	S	421010200002	BG	G5030	2	Block Group 2	208703717019432	42	020000	None	70	-075.1507409	+40.0042065	-075.1507409	+40.0042065	497	747	328094.605123	3138.906384
4	POLYGON ((-75.15216 40.01254, -75.15189 40.01199, -75.15143 40.01090, -75.15107 40.01004, -75.15103 40.00990, -75.15100 40.00982, -75.15081 40.00920, -75.15046 40.00806, -75.15029 40.00750, -75.14...	921	212571	212571	1	101	NaN	NaN	S	421010203001	BG	G5030	1	Block Group 1	208703717019433	42	020300	None	70	-075.1492793	+40.0091183	-075.1492793	+40.0091183	704	1354	362759.782648	3386.729282

	geometry	field
34138	POINT (-75.08299 40.01867)	Other
34139	POINT (-75.24638 39.96198)	Other
34140	POINT (-75.08122 40.02797)	Other
34141	POINT (-75.05724 40.01659)	Other
34142	POINT (-75.21985 39.93105)	Other

Week 5AGetting Data Part 1: Working with APIs

Part 1: Introduction to APIs¶

Example #1: Automated data feeds¶

USGS real-time earthquake feeds¶

Example #2: GeoServices¶

Philadelphia ZIP codes¶

Small utility package to make querying GeoServices easier: esri2gpd¶

Another useful example: census tracts¶

Example #3: Downloading Philly data from CARTO¶

SQL Databases¶

General Query Syntax¶

Let's try it out in Python¶

At-Home Exercise: Visualizing shootings data¶

Step 1: Prep the data¶

Step 2: Plot the points¶

Step 3: Make a (more useful) hex bin map¶

Count the total number of rows in a table¶

Select all columns, limiting the total number returned¶

Select by specific column values¶

The easier way: carto2gpd¶

At-Home Exercise: Explore trends by month and day of week¶

Step 1: Convert the date column to DateTime objects¶

Step 2: Calculate number of shootings by month and day of week¶

Step 3: Make a heatmap using hvplot¶

Part 2: The Census API¶

US Census data is foundational¶

Getting census data (the old way)American Factfinder

Getting census data (the new way)census.data.gov

Getting census data: other options¶

Data USA¶

Census Reporter:¶

NHGIS¶

Example: poverty data¶

Via the API¶

How to find the right variable names?¶

A detailed Census API guide¶

Accessing the API is easier from Python¶

Example: the racial "dot" map¶

The "explorer" module¶

Step 1: Identify what dataset we want¶

Step 2: Initialize the API connection¶

Step 3: Find the variables we want to load¶

Step 4: Identify the geographies to use¶

Step 5: Build the query (finally)¶

Step 6: We need the block group geometries too!¶

Step 7: Merge the demographic data with geometries¶

We're done! Plot it to make sure it makes sense¶

Now let's prep the data for the dot map¶

Define a function to create random points¶

Calculate the dot map¶

Now let's plot it¶

Exercise: lead poisoning in Philadelphia¶

Step 1. Download the demographic data for Philadelphia¶

Step 2. Download and merge in the census tract geometries¶

Use esri2gpd to load the data¶

Step 3. Calculate the black percentage¶

Step 4. Query CARTO to get the childhood lead levels by census tract¶

Step 5. Remove census tracts with missing lead measurements¶

Step 6. Merge the demographic and lead level data frames¶

Step 7. Trim to the columns we need¶

Step 8. Plot the results¶

Challenge: Step 9. Use seaborn to plot a 2d density map¶

Week 5A
Getting Data Part 1: Working with APIs

Small utility package to make querying GeoServices easier: `esri2gpd`¶

The easier way: `carto2gpd`¶

Step 1: Convert the date column to `DateTime` objects¶

Step 3: Make a heatmap using `hvplot`¶

Getting census data (the old way)
American Factfinder

Getting census data (the new way)
census.data.gov